package DB_sqlite;
use strict;
#use warnings;
use Data::Dumper;
use DBI;

sub error{ shift->{error} };

sub new{
 my $pkg = shift;
 my $c = shift;
 my $self = {
    c => $c, 
    dbh => undef,
    news_table => "news",
    news_id => "news_id",
    
 };

 return bless $self;
}

sub dbh{
 my $self = shift;
 $self->{c}->debug_call( "call dbh \n" );
 if ( !$self->{dbh} ){
    my $data_source = $self->{c}->conf("data_source") or $self->{error} = "data_source!", return 0;
#    die  Dumper  $data_source;
    my $dbh = DBI->connect( "$data_source","","" ) or die DBI::errstr . "($data_source)";
    #die Dumper $dbh;
    $self->{dbh} = $dbh; #DBI::connect( $data_source,"","" );

#    For stop and debug connection in this place:
#    die Dumper $self->{dbh};
#    my @ary = DBI->available_drivers; die Dumper "available drivers = " , \@ary;
#    my %drv = DBI->installed_drivers; die Dumper \%drv;
    
 }
 return $self->{dbh};
}

sub do_sql{
 # usage $self->do_sql( { sql=>'sql_statement', attrib=>'', bind_values=>[ 1,3,7 ] }); # see perldoc DBI, do().
 # or: $self->do_sql( { sql=>'sql_statement', bind_values=>[ 1,3,7 ] });
 # or: $self->do_sql( { sql=>'sql_statement',} );
 my $self = shift;
 my %pars = @_;
 %pars or $self->{error} = "params hash_ref! usage: $self->update({ param1=> val1, ... }) (".__FILE__." line: ".__LINE__."):\n $self->{error}", return 0;
 my @bind_values = @{ $pars{bind_values} } if ref $pars{bind_values} eq "ARRAY";
 $pars{sql} or $self->{error} = "param 'sql'! (".__FILE__." line: ".__LINE__."):\n $self->{error}", return 0;
 $self->{sql} = { sql=>$pars{sql},bind_values=>$pars{bind_values} }; # save sql, bind_values to debug
 my $dbh = $self->dbh() or $self->{error} = "Can't call dbh() (".__FILE__." line: ".__LINE__."):\n $self->{error}", return 0;
 my $affected = $dbh->do( $pars{sql}, $pars{attrib}||undef, @bind_values ) or die $dbh->errstr."; sql=$pars{sql}; bind_values=".Dumper(\@bind_values);
 return $affected;
}

sub set_news{
 my $self = shift;
 $self->{c}->debug_call( "call set_news \n" ); 
 my $news = shift;
 my $pars = shift;
 my %pars;
 %pars = %$pars if ref $pars eq "HASH";
 my $update_on_exist = $pars{update} ? 1 : 0;
 $self->{error} = " '\$news' most be a hash_ref! ".__FILE__." line: ".__LINE__."\n", return 0 if !ref $news;
 my %news = %$news;
 my $error;
 # site, id, url, small_text 
 $news{site} 		or $error = "Parameter 'site!'";
 $news{id} 		or $error = "Parameter 'id'!";
 $news{url} 		or $error = "Parameter 'url'!";
 $news{small_text} 	or $error = "Parameter 'small_text'!";
 $news{customer}||="";
 $self->{error} = $error." (".__FILE__." line: ".__LINE__.")", return 0 if $error;

 my $dbh = $self->dbh() or $self->{error} = "Can't call dbh() (".__FILE__." line: ".__LINE__."):\n $self->{error}", return 0;
 
 my $sql1 = "SELECT * FROM $self->{news_table} WHERE site=? AND id=?";
 my $sth1 = $dbh->prepare( $sql1 ) or $self->{error} = $dbh->errstr, return 0;
 $sth1->execute( @news{'site','id'} ) or $self->{error} = $dbh->errstr, return 0;
 while ( my $r = $sth1->fetchrow_hashref() ){
    return 2 if ! $update_on_exist; # 'already exist'
    my $sql3 = "UPDATE $self->{news_table} SET url = ?, small_text = ?, customer=? WHERE site = ? AND id = ? ";
    my $sth3 = $dbh->prepare($sql3);
    $sth3->execute( @news{qw/ url small_text customer site id /} );
    return 3; # - exist and updated
 }
 
 my $sql2 = "INSERT INTO $self->{news_table} ( site,id,url,small_text, customer, datetime, who_take, who_reject ) VALUES ( ?,?,?,?,?,?,'','' )";
 my $sth2 = $dbh->prepare( $sql2 );
 my $datetime = $self->{c}->my_date or die "Can't get datetime. ";
 my $rv = $sth2->execute( @news{qw/ site id url small_text customer /}, $datetime );
 $rv or $self->{error} = $sth2->errstr.".  ".__FILE__." line: ".__LINE__.").", return 0;
 
 return $rv;
}



sub find{
 # find in table $pars{table}
 # return a $sth object
 my $self = shift;
 my %pars = @_;
 my @where;
 $pars{id} ||= 0 if exists $pars{id};
 push @where , "$self->{news_id}=$pars{id}" if exists $pars{id};
 push @where , "$pars{where}" if $pars{where};
 my $sql = $pars{sql};
 my $where =  " WHERE ".join(" AND " , @where) if @where;
 my $order_by = $pars{order_by} ?" ORDER BY $pars{order_by} ":"";
 my $limit = $pars{limit} ? " LIMIT $pars{limit}":"";
 my $table = $pars{table};
 $table||$sql or $self->{error} = "Param 'table' or 'sql'! (".__FILE__." line: ".__LINE__."). ", return 0;
 my $fields = $pars{fields}||"*";
 
 my $q;
 $q = $sql||"SELECT $fields FROM $table $where $order_by $limit";
 $self->{sql} = $q;
 # die $q;
 #my $sth_name = $pars{sth_name}||"sth";
# die Dumper $self->dbh();
 #$self->{$sth_name} = $self->dbh()->prepare($q) or ( $self->{error} = $self->dbh->errstr , return 0 );
 my $sth = $self->dbh()->prepare($q) or ( $self->{error} = $self->dbh->errstr.". SQL query=\{$q\} ".__FILE__." line: ".__LINE__.") " , return 0 );
# $self->{$sth_name}->execute() or ( $self->{error} = $self->dbh->errstr , return 0);
 $sth->execute() or ( $self->{error} = $self->dbh->errstr." ".__FILE__." line: ".__LINE__.") " , return 0);
 return $sth;
}


1;
